﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DevExpress.Web;
using Ionic.Zip;
using Microsoft.Office.Interop.Excel;
using TheHeGeo.Util;
using TheHeGeoDao;
using DataTable = System.Data.DataTable;

namespace TheHeGeo.UserControl
{
    public partial class UcDownloadHistory : System.Web.UI.UserControl
    {
        public string maSensor { set; get; }
        public static string tenSensor { set; get; }
        public int maDuan { get; set; }
        public static string tenDuan { get; set; }
        public static string diaDiem { get; set; }
        public static string hangMuc { get; set; }
        public static string ghiChu { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                
                var db = new TheheGeoDbEntities();
                tenSensor = (from da in db.Sensors where da.MaSensor == maSensor select da.TenSensor).FirstOrDefault();
                Session["tenSensor"] = tenSensor;
                var duAn = (from da in db.DuAns where da.MaDuAn == maDuan select da).FirstOrDefault();
                tenDuan = duAn.TenDuAn;
                diaDiem = duAn.DiaDiem;
                hangMuc = duAn.HangMucQuanTrac;
                SensorName.Text = "Download dữ liệu history của sensor: " + tenSensor + " / Dự án: " + tenDuan;
                LoadTestData();
            }
        }
        protected void LoadTestData()
        {
            grlDataTest.GridView.KeyFieldName = "Id";
            var db = new TheheGeoDbEntities();
            var dt =
                (from t in db.DuLieuTests 
                 where t.MaSensor == maSensor && t.ThoiGianKetThucTest != null 
                 select t).ToList();
             grlDataTest.DataSource = dt;
             grlDataTest.DataBind();
         
            var dr = dt.LastOrDefault();
            if (dr != null)
            {
                grlDataTest.Value = dr.Id;
                if (dr.ThoiGianBatDauTest != null)
                {
                    deFrom.Date = (DateTime)dr.ThoiGianBatDauTest;
                    deFrom.MinDate = (DateTime)dr.ThoiGianBatDauTest;
                }
                else deFrom.Text = string.Empty;
                if (dr.ThoiGianKetThucTest != null)
                {
                    deTo.Date = (DateTime)dr.ThoiGianKetThucTest;
                    deTo.MaxDate = (DateTime)dr.ThoiGianKetThucTest;
                }
                else deTo.Text = string.Empty;

            }

        }
        protected void grlDataTest_ValueChanged(object sender, EventArgs e)
        {
            ghiChu = grlDataTest.GridView.GetRowValuesByKeyValue(grlDataTest.Value, "GhiChuTest").ToString();
            var temp = grlDataTest.GridView.GetRowValuesByKeyValue(grlDataTest.Value, "ThoiGianBatDauTest");
            if (temp != null)
            {
                deFrom.Date = (DateTime)temp;
                deFrom.MinDate = (DateTime)temp;
            }

            else deFrom.Text = string.Empty;

            temp = grlDataTest.GridView.GetRowValuesByKeyValue(grlDataTest.Value, "ThoiGianKetThucTest");

            if (temp != null)
            {
                deTo.Date = (DateTime)temp;
                deTo.MaxDate = (DateTime)temp;
            }
            else deTo.Text = string.Empty;
        }
        protected void btnDownload_Click(object sender, EventArgs e)
        {
            var rs = (from q in DaoHelper.Entities.SensorValuesHistories

                where (q.MaDuAn == maDuan && q.MaSensor == maSensor) &&
                      (string.IsNullOrEmpty(deFrom.Text) || q.ThoiGianDo >= deFrom.Date)
                      && (string.IsNullOrEmpty(deTo.Text) || q.ThoiGianDo <= deTo.Date)
                select new
                       {
                           q.ThoiGianDo,
                           q.GiaTri
                       }
                ).OrderByDescending(q => q.ThoiGianDo).ToList();
            if (rs.Count == 0)
                return;
            int count = 0;

            int numbFile = 0;
            while (count < rs.Count)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("ThoiGianDo", typeof (string));
                dt.Columns.Add("GiaTri", typeof (string));
                for (int i = 0; i < 100000; i++, count++)
                {
                    if (count == rs.Count)
                        break;
                    DataRow dr = dt.NewRow();
                    dr["ThoiGianDo"] = rs[count].ThoiGianDo.ToString("dd-MM-yy hh-mm-ss-ff");
                    dr["GiaTri"] = rs[count].GiaTri.ToString();
                    dt.Rows.Add(dr);

                }
                WriteCsvFile(dt, numbFile);
                numbFile++;
            }
            string folder = "../ExcelFile/" +
                                     ((SessionUser)Session[SessionConst.SES_USER]).NguoiSuDung.UserName;
            string newPath = Server.MapPath(folder);
            string[] files = Directory.GetFiles(newPath);
            if (files.Length == 0)
                return;
            Response.Clear();
            Response.ContentType = "application/zip";
            Response.AddHeader("content-disposition", "filename=" + "data.zip");
            using (ZipFile zip = new ZipFile())
            {
                foreach (string file in files)
                {
                    zip.AddFile(file);
                }
                zip.Save(Response.OutputStream);
            }
            foreach (string file in files)
            {
                File.Delete(file);
            }
        }

        public void WriteCsvFile(DataTable dt, int numbFile)
        {
            string subFile = "";
            if (numbFile > 0)
                subFile = "_" + numbFile.ToString();
            string filename = tenSensor + "_" + ghiChu + "_ExcelExport" + subFile + ".csv";
            //string newFilePath = Server.MapPath("/ExcelFile/" + filename);
            string folder = "../ExcelFile/" +
                                      ((SessionUser)Session[SessionConst.SES_USER]).NguoiSuDung.UserName;
            string newPath = Server.MapPath(folder);
            if (!Directory.Exists(newPath))
            {
                Directory.CreateDirectory(newPath);
            }
            string newFilePath = newPath + "\\" + filename;
            using (FileStream file = new FileStream(newFilePath, FileMode.CreateNew))
            {
                using (StreamWriter writer = new StreamWriter(file, Encoding.UTF8, 512))
                {
                    string line = "Tên dự án:," + tenDuan;
                    writer.WriteLine(line);
                    line = "Tên cảm biến:," + tenSensor;
                    writer.WriteLine(line);
                    line = "Địa điểm:," + diaDiem;
                    writer.WriteLine(line);
                    line = "Hạng mục:," + hangMuc;
                    writer.WriteLine(line);
                    writer.WriteLine("");
                    line = "Thời gian đo, Giá trị";
                    writer.WriteLine(line);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        line = dt.Rows[i]["ThoiGianDo"].ToString();
                        line += ",";
                        line += dt.Rows[i]["GiaTri"].ToString();
                        writer.WriteLine(line);
                    }
                }
            }  
        }
        public void WriteFileExcel(DataTable dt, int numbFile)
        {
            string subFile = "";
            if (numbFile > 0)
                subFile = "_" + numbFile.ToString();
            string filename = tenSensor + "_" + ghiChu + "_ExcelExport" + subFile + ".xlsx";
            //string newFilePath = Server.MapPath("/ExcelFile/" + filename);
            string folder = "../ExcelFile/" +
                                      ((SessionUser)Session[SessionConst.SES_USER]).NguoiSuDung.UserName;
            string newPath = Server.MapPath(folder);
            if (!Directory.Exists(newPath))
            {
                Directory.CreateDirectory(newPath);
            }
            string newFilePath = newPath + "\\" + filename;
            Application objExcel = null;
            Workbooks objBooks = null;
            _Workbook objBook = null;
            Sheets objSheets = null;
            _Worksheet objSheet = null;
            Range objRange = null;
            int row = 1, col = 1;
            try
                {
                   objExcel = new Application();
                   objBooks = objExcel.Workbooks;
                   objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
                    //Print column heading in the excel sheet
                    int j = col;
                    objSheets = objBook.Worksheets;
                    objSheet = (_Worksheet)objSheets.get_Item(1);
                    
                    //objRange = (Range)objSheet.Cells[row, j];
                    objRange = objSheet.get_Range("A1", "D1");
                    objRange.Merge();
                    objRange.Value2 = "Dự án: " + tenDuan;
                    objRange.WrapText = true;
                    row++;
                    objRange = objSheet.get_Range("A2", "D2");
                    objRange.Merge();
                    objRange.Value2 = "Hạng mục: " + hangMuc;
                    objRange.WrapText = true;
                    row++;
                    objRange = objSheet.get_Range("A3", "D3");
                    objRange.Merge();
                    objRange.Value2 = "Địa điểm: " + diaDiem;
                    objRange.WrapText = true;
                    row++;
                    objRange = objSheet.get_Range("A4", "D4");
                    objRange.Merge();
                    objRange.Value2 = "Tên sensor: " + tenSensor;
                    objRange.WrapText = true;
                    row++;
                    foreach (DataColumn column in dt.Columns)
                        {
                            objSheets = objBook.Worksheets;
                            objSheet = (_Worksheet)objSheets.get_Item(1);
                            objRange = (Range)objSheet.Cells[row, j];
                            objRange.Value2 = column.ColumnName;
                            objRange.ColumnWidth = 20;
                            j++;
                        }
                        row++;
                        int count = dt.Columns.Count;
                       
                        foreach (DataRow dataRow in dt.Rows)
                        {
                            int k = col;
                            for (int i = 0; i < count; i++)
                            {
                                objRange = (Range)objSheet.Cells[row, k];
                                objRange.Value2 = dataRow[i].ToString();
                                k++;
                            }
                        row++;
                        }
                        //Save Excel document
                        objSheet.Name = "Sample Sheet";
                        object objOpt = Missing.Value;
                        objBook.SaveAs(newFilePath);
                        objBook.Close(false, objOpt, objOpt);

                }
            catch
                {
                }
            finally
                {
                    objExcel = null;
                    objBooks = null;
                    objBook = null;
                    objSheets = null;
                    objSheet = null;
                    objRange = null;
                    ReleaseComObject(objExcel);
                    ReleaseComObject(objBooks);
                    ReleaseComObject(objBook);
                    ReleaseComObject(objSheets);
                    ReleaseComObject(objSheet);
                    ReleaseComObject(objRange);
                }
        }
        public void ReleaseComObject(object reference)
        {
            try
            {
                while (System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) <= 0)
                {
                }
            }
            catch
            {
            }
        } 
    }
}